package db.tables;


import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;


import control.ConnectionManage;
import db.parking.beans.Orders;



/**
 * AdminOrders manages all requests From,To table Orders
 * @author Operations
 *
 */
public class AdminOrders {
	
	private static Connection conn = ConnectionManage.getInstance().getConnection();

	/**
	 * Functions returns an array of all the orders in database by specified interval from DB table orders
	 * @return
	 * @throws SQLException
	 */
	public static Orders[] getAllOrders() throws SQLException {
		String sql = "SELECT * FROM orders WHERE leaveTime >= ? AND lotNo = ? AND (status=1 OR status=0 ) ORDER BY leaveTime ASC";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {

			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Orders bean[] = new Orders[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Orders();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setOid(rs.getInt("oid"));
				bean[rowCount].setUid(rs.getString("uid"));
				bean[rowCount].setType(rs.getString("type"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setStartTime(rs.getTimestamp("startTime"));
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setLeaveTime(rs.getTimestamp("leaveTime"));
				bean[rowCount].setEmail(rs.getString("email"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}

			return bean;

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}

	/**
	 * Get row from DB by id
	 * @param oId
	 * @return
	 * @throws SQLException
	 */
	public static Orders getRow(int oId) throws SQLException {

		String sql = "SELECT * FROM Orders WHERE oid = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1, oId);
			rs = stmt.executeQuery();

			if (rs.next()) {
				Orders bean = new Orders();
				bean.setOid(oId);
				bean.setUid(rs.getString("uid"));
				bean.setType(rs.getString("type"));
				bean.setCarNo(rs.getString("carNo"));
				bean.setStartTime(rs.getTimestamp("startTime"));
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setLeaveTime(rs.getTimestamp("leaveTime"));			
				bean.setEmail(rs.getString("email"));
				bean.setStatus(rs.getInt("status"));
				
				
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	/****
	 * getOrdersCountPerWeek**
	 * 
	 * @param theStartDay
	 * @return int  count of order per day
	 */
	public static int ordersCount(Timestamp startDay) throws SQLException {		
		int numOfDays;
		String sql = "SELECT count(*) FROM Orders WHERE startTime <= ? AND leaveTime >= ? status <>3";		//order is active during the day
		//calc next day second day
		Timestamp endDay= new Timestamp(startDay.getTime()); 
		Calendar cal = Calendar.getInstance();
		cal.setTime(startDay);
		cal.add(Calendar.DAY_OF_WEEK,1);  //add day for the first week
		endDay.setTime(cal.getTime().getTime()); //add day for the first week
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				) {
			stmt.setTimestamp(1, startDay);
			stmt.setTimestamp(2, endDay);
			rs = stmt.executeQuery();
			rs.last();
			numOfDays = rs.getRow();
			return numOfDays;
	}catch (SQLException e) {
		System.err.println(e);
		return 0;
	}
}



	
	public static Orders getRow(String uid,String carNo) throws SQLException {

		String sql = "SELECT * FROM Orders WHERE uid = ? AND carNo =?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setString(1, uid);
			stmt.setString(2, carNo);

			rs = stmt.executeQuery();

			if (rs.next()) {
				Orders bean = new Orders();
				bean.setOid(rs.getInt("oid"));
				bean.setUid(rs.getString("uid"));
				bean.setType(rs.getString("type"));
				bean.setCarNo(rs.getString("carNo"));
				bean.setStartTime(rs.getTimestamp("startTime"));
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setLeaveTime(rs.getTimestamp("leaveTime"));			
				bean.setEmail(rs.getString("email"));
				bean.setStatus(rs.getInt("status"));
				return bean;
			} else {
				return null;
			}
		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	


	public static Orders[] getRow(Timestamp currTime,int lotNo) throws SQLException {
		String sql = "SELECT * FROM orders WHERE leaveTime >= ? AND lotNo = ? AND (status=1 OR status=0 ) ORDER BY leaveTime ASC";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, currTime);
			stmt.setInt(2, lotNo);
			
			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Orders bean[] = new Orders[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Orders();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setOid(rs.getInt("oid"));
				bean[rowCount].setUid(rs.getString("uid"));
				bean[rowCount].setType(rs.getString("type"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setStartTime(rs.getTimestamp("startTime"));
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setLeaveTime(rs.getTimestamp("leaveTime"));
				bean[rowCount].setEmail(rs.getString("email"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}

			return bean;

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	
	/**
	 * Function returns all Full Subscribers which are currently parking in the lot
	 * @return
	 * @throws SQLException
	 */
	public static Orders[] getAllFullSubcriber() throws SQLException {
		String sql = "SELECT * FROM Orders WHERE type = 'Full' AND status = 1";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			
			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Orders bean[] = new Orders[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Orders();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setOid(rs.getInt("oid"));
				bean[rowCount].setUid(rs.getString("uid"));
				bean[rowCount].setType(rs.getString("type"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setStartTime(rs.getTimestamp("startTime"));
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setLeaveTime(rs.getTimestamp("leaveTime"));
				bean[rowCount].setEmail(rs.getString("email"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}

			return bean;

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	
	/**
	 * Functions sorts orders by specified interval by type
	 * @param startTime
	 * @param leaveTime
	 * @param lotNo
	 * @return
	 * @throws SQLException
	 */
	public static Orders[] getOrdersDuringTimeBylotNo(Timestamp startTime, Timestamp leaveTime, int lotNo) throws SQLException {
		String sql = "SELECT * FROM orders WHERE startTime >= ? AND startTime < ? AND lotNo = ? ORDER BY type";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, startTime);
			stmt.setTimestamp(2, leaveTime);
			stmt.setInt(3, lotNo);
			
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Orders bean[] = new Orders[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Orders();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setOid(rs.getInt("oid"));
				bean[rowCount].setUid(rs.getString("uid"));
				bean[rowCount].setType(rs.getString("type"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setStartTime(rs.getTimestamp("startTime"));
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setLeaveTime(rs.getTimestamp("leaveTime"));
				bean[rowCount].setEmail(rs.getString("email"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}
			return bean;
		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}
	
	/**
	 * Functions gets all orders which are after the currentTime
	 * @param currTime
	 * @return
	 * @throws SQLException
	 */
	public static Orders[] getLateOrders(Timestamp currTime) throws SQLException {
		String sql = "SELECT * FROM Orders WHERE ? > startTime AND status = 0";
		
		ResultSet rs = null;
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				) {
			stmt.setTimestamp(1, currTime);

			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Orders bean[] = new Orders[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Orders();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setOid(rs.getInt("oid"));
				bean[rowCount].setUid(rs.getString("uid"));
				bean[rowCount].setType(rs.getString("type"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setStartTime(rs.getTimestamp("startTime"));
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setLeaveTime(rs.getTimestamp("leaveTime"));
				bean[rowCount].setEmail(rs.getString("email"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}

			return bean;

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	/**
	 * Functions deletes Subscriber after Subscription END from DB table Orders
	 * @param currTime
	 * @return
	 * @throws SQLException
	 */
	public static Orders[] KickSubscriber(Timestamp currTime) throws SQLException {
		String sql = "SELECT * FROM Orders WHERE ? > startTime AND status = 1";
		
		ResultSet rs = null;
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				) {
			stmt.setTimestamp(1, currTime);

			

			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Orders bean[] = new Orders[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Orders();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setOid(rs.getInt("oid"));
				bean[rowCount].setUid(rs.getString("uid"));
				bean[rowCount].setType(rs.getString("type"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setStartTime(rs.getTimestamp("startTime"));
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setLeaveTime(rs.getTimestamp("leaveTime"));
				bean[rowCount].setEmail(rs.getString("email"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}

			return bean;

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	
	/**
	 * Get all orders which needed to be canceled
	 * @param currTime
	 * @return
	 * @throws SQLException
	 */
	public static Orders[] getOrdersToCancel(Timestamp currTime) throws SQLException {
		String sql = "SELECT * FROM Orders WHERE ? > startTime AND status = 0";
		
		ResultSet rs = null;
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				) {
			stmt.setTimestamp(1, currTime);

			

			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Orders bean[] = new Orders[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Orders();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setOid(rs.getInt("oid"));
				bean[rowCount].setUid(rs.getString("uid"));
				bean[rowCount].setType(rs.getString("type"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setStartTime(rs.getTimestamp("startTime"));
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setLeaveTime(rs.getTimestamp("leaveTime"));
				bean[rowCount].setEmail(rs.getString("email"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}

			return bean;

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	
	
	/**
	 * Check lot status and if inserting more cars is possible atm.
	 * @param bean
	 * @return
	 * @throws SQLException
	 */
	public static int getLotStatus(Orders bean) throws SQLException {
		String sql = "SELECT * FROM Orders WHERE startTime >= ? AND leaveTime < ? AND lotNo = ?";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, bean.getStartTime());
			stmt.setTimestamp(2, bean.getLeaveTime());
			stmt.setInt(3, bean.getLotNo());
			
			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();		
		
			return totalRows;

		} catch (SQLException e) {
			System.err.println(e);
			return 0;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	

	public static boolean insert(Orders bean) throws Exception {
    //order id is autoIncremented on Insert

		String sql = "INSERT into Orders (uid , type , carNo , startTime, leaveTime , lotNo ,  email , status) " +
				"VALUES (?, ? ,? ,? ,? , ? , ? , ?)";
		ResultSet keys = null;
		try (
				PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				) {
			stmt.setString(1, bean.getUid());
			stmt.setString(2, bean.getType());
			stmt.setString(3, bean.getCarNo());
			stmt.setTimestamp(4,bean.getStartTime());
			stmt.setTimestamp(5,bean.getLeaveTime());
			stmt.setInt(6, bean.getLotNo());
			stmt.setString(7, bean.getEmail());
			stmt.setInt(8, bean.getStatus());
			int affected = stmt.executeUpdate();
			
			if(affected == 1){
				keys = stmt.getGeneratedKeys();
				keys.next();
				int newKey = keys.getInt(1);
				bean.setOid(newKey);
			}
			else{
				System.err.println("No rows affected");
				return false;
			}
			
		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally{
			if(keys != null)
				keys.close();
		}
		return true;
	}
	
	public static boolean insertOccasional(Orders bean) throws Exception {
	    //order id is autoIncremented on Insert

			String sql = "INSERT into Orders (uid , type , carNo , leaveTime , lotNo ,  email , status) " +
					"VALUES (?, ? ,? ,? , ? , ? , ?)";
			ResultSet keys = null;
			try (
					PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					) {
				
				stmt.setString(1, bean.getUid());
				stmt.setString(2, bean.getType());
				stmt.setString(3, bean.getCarNo());
				stmt.setTimestamp(4,bean.getLeaveTime());
				stmt.setInt(5, bean.getLotNo());
				stmt.setString(6, bean.getEmail());
				stmt.setInt(7, bean.getStatus());
				int affected = stmt.executeUpdate();
				
				if(affected == 1){
					keys = stmt.getGeneratedKeys();
					keys.next();
					int newKey = keys.getInt(1);
					bean.setOid(newKey);
				}
				else{
					System.err.println("No rows affected");
					return false;
				}
				
			} catch (SQLException e) {
				System.err.println(e);
				return false;
			} finally{
				if(keys != null)
					keys.close();
			}
			return true;
			
		}
	//update order status
	/**
	 * Update an order in DB table orders
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean update(Orders bean) throws Exception {

		String sql =
				"UPDATE Orders SET " +
				"status = ? " +
				"WHERE oid = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,bean.getStatus());
			stmt.setInt(2,bean.getOid());
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	
	
	
	/**
	 * Update the status of a subscriber which has finished his subscription
	 * @param cusID
	 * @throws Exception
	 */
	public static void updateFinishedSubscriber(String cusID) throws Exception {

		String sql =
				"UPDATE Orders SET " +
				"status = 3 " +
				"WHERE (type ='regular' OR type='Full') AND uid = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setString(1,cusID);
			stmt.executeUpdate();
		}
		catch(SQLException e) {
			System.err.println(e);
			return;
		}

	}
	
	//update start time
	public static boolean updateOccasionalTime(Orders bean) throws Exception {

		String sql =
				"UPDATE Orders SET " +
				"startTime = ? " +
				"WHERE oid = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setTimestamp(1,bean.getStartTime());
			stmt.setInt(2,bean.getOid());
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	
	//delete order by id
	public static boolean delete(int oId) throws Exception {

		String sql = "DELETE FROM Orders WHERE oid = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,oId);
			int affected = stmt.executeUpdate();
			if(affected == 1){
					return true;
			} else {
				return false;
			}
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}
	}
	
	
	/**
	 * Functions returns the number of temporary orders that are actually done
	 * @param startDay
	 * @param endDay
	 * @param lotNo
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("deprecation")
	public static int numberOfRealizedOrders(Timestamp startDay,Timestamp endDay, int lotNo) throws Exception {
		//return num of temporary orders the actually done	
		String sql = "SELECT o.oid " +
				"FROM orders o JOIN checkin c ON o.oid = c.oid " +
				"WHERE  c.timestamp >= ? AND c.timestamp < ? AND o.type = ? AND o.lotNo=?";
		// calc the start of the day like 2014-02-26 00:00:00 000
		

		startDay.setHours(0);
		startDay.setMinutes(0);
		startDay.setSeconds(0);
		startDay.setNanos(0);
		
		ResultSet rs = null;

		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, startDay);
			stmt.setTimestamp(2, endDay);
			stmt.setString(3, "Temporary");
			stmt.setInt(4, lotNo);
			rs = stmt.executeQuery();
			rs.last();
			int numOfOrders = rs.getRow();
			return numOfOrders;		
		} catch (SQLException e) {
			System.err.println(e+ "AdminOrders-> numberOfRealizedOrders");
			return -1;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}
	
	/**
	 * Function returns the number of temporary orders which are late for the scheduled parking time
	 * @param startDay
	 * @param endDay
	 * @param lotNo
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("deprecation")
	public static int numberOfParkingLateness(Timestamp startDay,Timestamp endDay, int lotNo) throws Exception {
		//return num of temporary orders that leate for the parking time	
		String sql = "SELECT o.oid " +
				"FROM orders o JOIN checkin c ON o.oid = c.oid " +
				"WHERE  c.timestamp >= ? AND c.timestamp < ? AND o.type = ? AND o.lotNo=? AND c.timestamp > o.startTime";
		// calc the start of the day like 2014-02-26 00:00:00 000
		
		startDay.setHours(0);
		startDay.setMinutes(0);
		startDay.setSeconds(0);
		startDay.setNanos(0);
		
		endDay.setHours(0);
		endDay.setMinutes(0);
		endDay.setSeconds(0);
		endDay.setNanos(0);
		

		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, startDay);
			stmt.setTimestamp(2, endDay);
			stmt.setString(3, "Temporary");
			stmt.setInt(4, lotNo);
			rs = stmt.executeQuery();
			rs.last();
			int numOfOrders = rs.getRow();
			return numOfOrders;		
		} catch (SQLException e) {
			System.err.println(e+ "AdminOrders-> numberOfParkingLateness");
			return -1;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}
}
	
	
